DROP TABLE IF EXISTS search;
DROP TABLE IF EXISTS marked_question;
DROP TABLE IF EXISTS account;
DROP TABLE IF EXISTS comment;
DROP TABLE IF EXISTS question_tag;
DROP TABLE IF EXISTS tag;
DROP TABLE IF EXISTS linked_question;
DROP TABLE IF EXISTS question CASCADE;
DROP TABLE IF EXISTS answer CASCADE;
DROP TABLE IF EXISTS author;
DROP TABLE IF EXISTS location;

CREATE TABLE location (
	id SERIAL PRIMARY KEY,
	location TEXT NOT NULL
);

CREATE TABLE author (
	id INTEGER PRIMARY KEY,
	display_name TEXT NOT NULL,
	creation_date TIMESTAMP WITHOUT TIME ZONE NOT NULL,
	age INTEGER,
	location_id INTEGER,
	CONSTRAINT author_location_location_id_fk FOREIGN KEY(location_id)
		REFERENCES location(id)
		ON UPDATE CASCADE
		ON DELETE CASCADE
);

CREATE TABLE tag (
	id SERIAL PRIMARY KEY,
	tag TEXT NOT NULL
);

CREATE TABLE question (
	id INTEGER PRIMARY KEY,
	title TEXT NOT NULL,
	body TEXT NOT NULL,
	score INTEGER NOT NULL,
	creation_date TIMESTAMP WITHOUT TIME ZONE NOT NULL,
	closed_date TIMESTAMP WITHOUT TIME ZONE,
	author_id INTEGER NOT NULL,
	accepted_answer_id INTEGER,
	CONSTRAINT question_author_author_id_fk FOREIGN KEY(author_id)
		REFERENCES author(id)
		ON UPDATE CASCADE
		ON DELETE CASCADE
);

CREATE TABLE answer (
	id INTEGER PRIMARY KEY,
	body TEXT NOT NULL,
	score INTEGER NOT NULL,
	creation_date TIMESTAMP WITHOUT TIME ZONE NOT NULL,
  question_id INTEGER,
	author_id INTEGER NOT NULL,
	CONSTRAINT answer_author_author_id_fk FOREIGN KEY(author_id)
		REFERENCES author(id)
		ON UPDATE CASCADE
		ON DELETE CASCADE,
    CONSTRAINT answer_question_question_id_fk FOREIGN KEY(question_id)
		REFERENCES question(id)
		ON UPDATE CASCADE
		ON DELETE CASCADE
);

ALTER TABLE question ADD CONSTRAINT question_answer_accepted_answer_id_fk FOREIGN KEY(accepted_answer_id)
	REFERENCES answer(id)
    ON UPDATE CASCADE
    ON DELETE CASCADE;

CREATE TABLE question_tag (
	question_id INTEGER NOT NULL,
	tag_id INTEGER NOT NULL,
	PRIMARY KEY(question_id, tag_id),
	CONSTRAINT question_tag_question_question_id_fk FOREIGN KEY(question_id)
		REFERENCES question(id)
		ON UPDATE CASCADE
		ON DELETE CASCADE,
	CONSTRAINT question_tag_tag_tag_id_fk FOREIGN KEY(tag_id)
		REFERENCES tag(id)
		ON UPDATE CASCADE
		ON DELETE CASCADE
);

CREATE TABLE linked_question (
	question_id INTEGER NOT NULL,
	linked_question_id INTEGER NOT NULL,
	PRIMARY KEY(question_id, linked_question_id),
	CONSTRAINT question_question_id_fk FOREIGN KEY(question_id)
		REFERENCES question(id)
		ON UPDATE CASCADE
		ON DELETE CASCADE,
	CONSTRAINT question_linked_question_id_fk FOREIGN KEY(linked_question_id)
		REFERENCES question(id)
		ON UPDATE CASCADE
		ON DELETE CASCADE
);

CREATE TABLE comment (
	id INTEGER PRIMARY KEY,
	text TEXT NOT NULL,
	score INTEGER NOT NULL,
	creation_date TIMESTAMP WITHOUT TIME ZONE NOT NULL,
	author_id INTEGER NOT NULL,
	question_id INTEGER,
	answer_id INTEGER,
	CONSTRAINT comment_author_author_id_fk FOREIGN KEY(author_id)
		REFERENCES author(id)
		ON UPDATE CASCADE
		ON DELETE CASCADE,
	CONSTRAINT comment_question_question_id_fk FOREIGN KEY(question_id)
		REFERENCES question(id)
		ON UPDATE CASCADE
		ON DELETE CASCADE,
	CONSTRAINT comment_answer_answer_id_fk FOREIGN KEY(answer_id)
		REFERENCES answer(id)
		ON UPDATE CASCADE
		ON DELETE CASCADE
);

CREATE TABLE account (
	id SERIAL PRIMARY KEY,
	name TEXT NOT NULL,
	email TEXT NOT NULL,
	password CHARACTER VARYING(60) NOT NULL
);

CREATE TABLE search (
	id BIGSERIAL PRIMARY KEY,
	search_text TEXT NOT NULL, 
	search_date TIMESTAMP WITHOUT TIME ZONE NOT NULL,
	account_id INTEGER NOT NULL,
	CONSTRAINT search_account_account_id_fk FOREIGN KEY(account_id)
		REFERENCES account(id)
		ON UPDATE CASCADE
		ON DELETE CASCADE
);

CREATE TABLE marked_question (
	account_id INTEGER NOT NULL,
	question_id INTEGER NOT NULL,
	annotation TEXT,
    PRIMARY KEY (account_id, question_id),
	CONSTRAINT marked_question_account_account_id_fk FOREIGN KEY(account_id)
		REFERENCES account(id)
		ON UPDATE CASCADE
		ON DELETE CASCADE,
    CONSTRAINT marked_question_question_question_id_fk FOREIGN KEY(question_id)
		REFERENCES question(id)
		ON UPDATE CASCADE
		ON DELETE CASCADE
);
